﻿create  database Student
on
(
	name='Student',
	filename='D:\Student.mdf',
	size=10,
	maxsize=100,
	filegrowth=10
)
log on
(
	name='Student_log',
	filename='D:\Student_log.ldf',
	size=10,
	maxsize=100,
	filegrowth=10
)
go
use Student
go
	create table ClassIfo
(
	ClassID int primary key  identity(1,1)  not null , 
	ClassName  nvarchar(20)  not null unique 
)
create table StudentIfo
(
	StuID int primary key identity(1,1),
	ClassID int foreign key references ClassIfo(ClassID) not null,
	StuName nvarchar(20) not null,
	StuSex  nchar(1) check(StuSex=''or StuSex='Ů') not null,
	StuBirthday  date,
	StuPhone  char(11) unique
)
create table Course
(
	CourseID int  primary key identity(1,1),
	CourseName nvarchar(50) unique not null,
	CourseCredit int default(1) check(CourseCredit=1 or CourseCredit=2  or CourseCredit=3 or CourseCredit=4 or CourseCredit=5) not null
)
create table Score
(
	ScoreID int  primary key identity(1,1),
	StuID int,
	CourseID int ,
	Score decimal(5,2) unique not null
)
alter table StudentIfo  add StuAddress nvarchar(200) 
alter table Score add constraint PK_Score_ScoreID primary key(ScoreID)
alter table Score add constraint FK_Score_StuID foreign key(StuID) references StudentIfo(StuID)
alter table Score add consstraint FK_Score_CourseID foreign key(CourseID) references Course(CourseID)
